/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package fr.p10.miage.gestionabsence.model;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Sofian Seid
 */
public class EtudiantBDD {

   public EtudiantBDD() {
       
   }

    public static Etudiant trouverParId(int id) throws SQLException, Exception {
        Etudiant etudiant = new Etudiant();
        String sql = "SELECT * FROM ETUDIANT WHERE ID = ?";
        PreparedStatement pst = Connection.getBDD().prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        pst.setInt(1, id);
        if (pst.execute()) {
            ResultSet resultat = pst.getResultSet();
            if (resultat.first() == true) {
                etudiant.init(resultat);
            } 
            resultat.close();
        }
        return etudiant;
    }
       
    public static ArrayList getList(int id) throws SQLException {
        ArrayList <Etudiant> etudiants = new ArrayList<Etudiant>();    
        String sql="SELECT * FROM ETUDIANT WHERE PROMOTION_ID = ?";
        PreparedStatement pst = Connection.getBDD().prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        pst.setInt(1,id);
        ResultSet resultat = pst.executeQuery();       
        while(resultat.next()){
            etudiants.add(new Etudiant(resultat));
        }
        resultat.close();
        return etudiants;    
    }
    
    public static Etudiant trouverParLoginEtPassword(String login, String password) throws SQLException, Exception {
      Etudiant etudiant = new Etudiant();
      String sql = "SELECT * FROM ETUDIANT WHERE MAIL = ?";
      PreparedStatement pst = Connection.getBDD().prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
      pst.setString(1, login);
      if (pst.execute()) {
         ResultSet resultat = pst.getResultSet();
         if (resultat.first() == true) {
            if (password.equals(resultat.getString("PASSWD"))) {
               etudiant.init(resultat);
            } else {
               throw new Exception("Mot de passe incorrect");
            }
         } else {
            throw new Exception("Utilisateur inconnu");
         }
         resultat.close();
      }
      return etudiant;
   }

    public static Etudiant signalerPresenceCours(int idEtudiant, int idAgenda) throws SQLException, Exception {
        Etudiant etudiant = new Etudiant();
        String sql = "INSERT INTO PRESENCE VALUES(?,?)";
        PreparedStatement pst = Connection.getBDD().prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        pst.setInt(1, idEtudiant);
        pst.setInt(2, idAgenda);
        if (pst.execute()) {
            ResultSet resultat = pst.getResultSet();
            if(resultat.first()==true) {
                if(resultat.last()==true) {
                    etudiant.init(resultat);
                } else {
                    throw new Exception("ID Agenda incorrect");
                }
            } else {
                throw new Exception("ID Etudiant incorrect");
            }
            resultat.close();
        }
        return etudiant;
    }
    
    public static ArrayList<Agenda> getAbsenceEtudiant(int e){
        try {
            ArrayList<Agenda> a = new ArrayList<Agenda>();
            String sql="SELECT IDCR, Jour.datee FROM AGENDA, JOUR"
                    + " WHERE IDCR NOT IN (SELECT AGENDA_ID FROM PRESENCE WHERE ETUDIANT_ID = ?)"
                    + " AND agenda.idj = jour.idj"
                    + " AND jour.datee > to_date(?, 'DD-MM-YY')"
                    + " AND jour.datee < to_date(?, 'DD-MM-YY')"
                    + " ORDER BY jour.datee ASC";
                     
            PreparedStatement pst = Connection.getBDD().prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            pst.setInt(1,e);
            
            Calendar rightNow = Calendar.getInstance();
            rightNow.set(rightNow.get(Calendar.YEAR), rightNow.get(Calendar.MONTH), 1);           
            SimpleDateFormat formater = new SimpleDateFormat("dd/MM/yy");
            pst.setString(2,formater.format(rightNow.getTime()).toString());
            rightNow = Calendar.getInstance();
            pst.setString(3, formater.format(rightNow.getTime()).toString());
            
            ResultSet resultat = pst.executeQuery();       
            while(resultat.next()){
                try {
                    a.add(AgendaBDD.trouverParId(resultat.getInt("IDCR")));
                } catch (Exception ex) {
                    Logger.getLogger(EtudiantBDD.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            return a;
        } catch (SQLException ex) {
            Logger.getLogger(EtudiantBDD.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }
}

       
